package com.wxd.framework.common.office;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelReader {
	
	private Workbook workbook = null;
	private Sheet sheet = null;
	private Row row = null;
	private int sheetNum = 0;//第sheetNum个工作表
	private int rowNum = 0;
	private InputStream inputStream = null; 
	private File file = null;
	
	public ExcelReader(){		
	}
	
	public ExcelReader(File file){
		this.file = file;
	}
	
	public void setRowNum(int rowNum){
		this.rowNum = rowNum;
	}
	
	public void setSheetNum(int sheetNum){
		this.sheetNum = sheetNum;
	}
	
	public void setFile(File file){
		this.file = file;
	}
	
	/**
	 * 返回sheet表数目
	 * @return
	 */
	public int getSheetCount(){
		if (workbook != null) {
			return workbook.getNumberOfSheets();
		}else {
			return -1;
		}
		
	}
	
	/**
	 * 获取sheetNum下的记录行数
	 * @return
	 */
	public int getRowCount(){
		if (workbook != null) {
			return getRowCount(this.sheetNum);	
		}else {
			return -1;
		}
	}
	
	/**
	 * 获取指定sheetNum下的记录行数
	 * @param sheetNum
	 * @return
	 */
	public int getRowCount(int sheetNum){
		Sheet sheet = workbook.getSheetAt(sheetNum);
		if (sheet != null) {
			return sheet.getLastRowNum();
		}else{
			return -1;
		}
	}
	
	/**
	 * 读取excel文件获得Workbook对象
	 * @throws IOException
	 */
	public void open() throws IOException{
		inputStream = new FileInputStream(file);
		String path = file.getPath().trim();
		int length = path.length();
		String lastChar = file.getPath().substring(length-1, length).toLowerCase();
		if("x".equals(lastChar)){
			workbook = new XSSFWorkbook(inputStream); 
		}else {
			workbook = new HSSFWorkbook(inputStream); 
		}
		inputStream.close();
	}
	
	/**
	 * 得到指定行的内容
	 * @param lineNum
	 * @return
	 */
	public String[] readExcelLine(int lineNum){
		return readExcelLine(this.sheetNum, lineNum);
	}
	
	/**
	 * 得到指定表和行的内容
	 * @param sheetNum
	 * @param lineNum
	 * @return
	 */
	public String[] readExcelLine(int sheetNum, int lineNum) {
		if (sheetNum < 0 || lineNum < 0){
			return null;
		}			
		String[] strExcelLine = null;
		try {
			sheet = workbook.getSheetAt(sheetNum);
			row = sheet.getRow(lineNum);
			int cellCount = row.getLastCellNum();
			strExcelLine = new String[cellCount + 1];
			for (int i = 0; i <= cellCount; i++) {
				strExcelLine[i] = readStringExcelCell(lineNum, i);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return strExcelLine;
	}
	
	/**
	 * 读取指定列的内容
	 * 
	 * @param cellNum
	 * @return String
	 */
	public String readStringExcelCell(int cellNum) {
		return readStringExcelCell(this.rowNum, cellNum);
	}

	/**
	 * 指定行和列编号的内容
	 * 
	 * @param rowNum
	 * @param cellNum
	 * @return String
	 */
	public String readStringExcelCell(int rowNum, int cellNum) {
		return readStringExcelCell(this.sheetNum, rowNum, cellNum);
	}
	
	/**
	 * 指定行和列编号的内容（强制获取Date 类型）
	 * @param rowNum
	 * @param cellNum
	 * @return
	 */
	public String readForciblyDateExcelCell(int rowNum, int cellNum){
		return readForciblyDateExcelCell(this.sheetNum, rowNum, cellNum);
	}
	
	
	/**
	 * 指定工作表、行、列下的内容
	 * 
	 * @param sheetNum
	 * @param rowNum
	 * @param cellNum
	 * @return String
	 */
	public String readStringExcelCell(int sheetNum, int rowNum, int cellNum) {
		if (sheetNum < 0 || rowNum < 0){
			return "";
		}			
		String strExcelCell = "";
		try {
			sheet = workbook.getSheetAt(sheetNum);
			row = sheet.getRow(rowNum);

			if (row.getCell((short) cellNum) != null) { // add this condition
				// judge
				switch (row.getCell((short) cellNum).getCellType()) {
				case HSSFCell.CELL_TYPE_FORMULA://获取计算公式的值
					double factor = Math.pow(10, 1);//设置数字和浮点位数
					double a =  Math.floor(row.getCell((short) cellNum).getNumericCellValue() * factor + 0.5) / factor;//保留一位小数(四舍五入)
					strExcelCell =String.valueOf(a);
					
					break;
				case HSSFCell.CELL_TYPE_NUMERIC: //获取数字
					if (HSSFDateUtil.isCellDateFormatted(row.getCell((short) cellNum))) {
						Date date = row.getCell((short) cellNum).getDateCellValue();
						DateFormat formater = new SimpleDateFormat("yyyy-MM-dd");
						strExcelCell = formater.format(date);
					}else {
						strExcelCell = String.valueOf(row.getCell((short) cellNum)
								.getNumericCellValue());
					}
					break;
				case HSSFCell.CELL_TYPE_STRING://获取字符串
					strExcelCell = row.getCell((short) cellNum)
							.getStringCellValue();
					break;
				case HSSFCell.CELL_TYPE_BLANK://空格
					strExcelCell = "";
					break;
				default:
					strExcelCell = "";
					break;
				}
			}
		} catch (Exception e) {
		}
		return strExcelCell;
	}
	
	/**
	 * 指定工作表、行、列下的内容（强制获取Date 类型）
	 * 
	 * @param sheetNum
	 * @param rowNum
	 * @param cellNum
	 * @return String
	 */
	public String readForciblyDateExcelCell(int sheetNum, int rowNum, int cellNum) {
		if (sheetNum < 0 || rowNum < 0)
			return "";
		String strExcelCell = "";
		try {
			sheet = workbook.getSheetAt(sheetNum);
			row = sheet.getRow(rowNum);

			if (row.getCell((short) cellNum) != null) { // add this condition
				Date date = row.getCell((short) cellNum).getDateCellValue();
				DateFormat formater = new SimpleDateFormat("yyyy-MM-dd");
				strExcelCell = formater.format(date);
			}
		} catch (Exception e) {
		}
		return strExcelCell;
	}
}	
